
import java.io.StringWriter;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.sql.Date;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.transform.OutputKeys;
import javax.xml.transform.Transformer;
import javax.xml.transform.TransformerConfigurationException;
import javax.xml.transform.TransformerException;
import javax.xml.transform.TransformerFactory;
import javax.xml.transform.dom.DOMSource;
import javax.xml.transform.stream.StreamResult;
import org.w3c.dom.Document;
import org.w3c.dom.Node;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
/**
 *
 * @author Patrick
 */
public class EventManager {

    private static ArrayList<Event> getEventTypes(Connection con) throws SQLException{
        ArrayList<Event> list = new ArrayList();
        String sql = "SELECT id as typeid, description from eventtype";
        PreparedStatement stat = con.prepareStatement(sql);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            Event e = new Event(rs.getInt("typeId"),rs.getString("description"));
            list.add(e);
        }
        rs.close();
        stat.close();
        return list;
    }
    private static ArrayList<Event> getEvents(Connection con, double lon, double lat) throws SQLException {
        ArrayList<Event> list = new ArrayList();
        String sql = "SELECT e.id as id, t.id as typeid, name, description, longitude, latitude, creationdate, endingdate  ";
        sql += " FROM events e inner join eventtype t on t.id = e.eventid ";
        sql += " where longitude between ? and ? and latitude between ? and ? ";
        sql += " order by endingdate desc, creationdate desc limit 200";
        double minLon = lon - 1;
        double minLat = lat - 1;
        double maxLon = lon + 1;
        double maxLat = lat + 1;
        PreparedStatement stat = con.prepareStatement(sql);
        stat.setDouble(1, minLon);
        stat.setDouble(2, maxLon);
        stat.setDouble(3, minLat);
        stat.setDouble(4, maxLat);
        ResultSet rs = stat.executeQuery();
        while (rs.next()) {
            Event e = new Event(rs,lon,lat);
            list.add(e);
        }
        rs.close();
        stat.close();
        return list;
    }

    public static void postEvent(Connection con, int eventId,String name, double lon, double lat,Date endingdate) throws SQLException {
        String sql = "INSERT INTO events (longitude,latitude,eventid,name,endingdate) values (?,?,?,?,?)";
        PreparedStatement stat = con.prepareStatement(sql);
        stat.setDouble(1, lon);
        stat.setDouble(2, lat);
        stat.setInt(3, eventId);
        stat.setString(4, name);
        stat.setDate(5, endingdate);
        stat.execute();
        stat.close();
    }

    public static String getXMLEventList(Connection con, double lon, double lat) throws ParserConfigurationException, TransformerConfigurationException, SQLException, TransformerException {
        Document doc = getBaseDocument();
        Node root = doc.getElementsByTagName("crombz").item(0);
        ArrayList<Event> list = getEvents(con, lon, lat);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        for (Event e : list) {
            Node event = doc.createElement("event");
            
            Node description = doc.createAttribute("description");
            Node name = doc.createAttribute("name");
            Node longitude = doc.createAttribute("longitude");
            Node latitude = doc.createAttribute("latitude");
            Node typeid = doc.createAttribute("typeid");
            Node id = doc.createAttribute("id");
            Node bearing = doc.createAttribute("bearing");
            Node distance = doc.createAttribute("distance");
            Node creationdate = doc.createAttribute("creationdate");
            Node endingdate = doc.createAttribute("endingdate");
            
            description.setNodeValue(e.getDescription());
            name.setNodeValue(e.getName());
            longitude.setNodeValue(e.getLongitude()+"");
            latitude.setNodeValue(e.getLatitude()+"");
            typeid.setNodeValue(e.getTypeId()+"");
            id.setNodeValue(e.getId()+"");
            bearing.setNodeValue(e.getBearing()+"");
            distance.setNodeValue(e.getDistance()+"");
            creationdate.setNodeValue(sdf.format(e.getCreationDate()));
            endingdate.setNodeValue(sdf.format(e.getEndingDate()));
            
            event.getAttributes().setNamedItem(description);
            event.getAttributes().setNamedItem(name);
            event.getAttributes().setNamedItem(id);
            event.getAttributes().setNamedItem(typeid);
            event.getAttributes().setNamedItem(longitude);
            event.getAttributes().setNamedItem(latitude);
            event.getAttributes().setNamedItem(creationdate);
            event.getAttributes().setNamedItem(endingdate);
            event.getAttributes().setNamedItem(bearing);
            event.getAttributes().setNamedItem(distance);
            root.appendChild(event);
        }
        list = getEventTypes(con);
        for (Event et : list){
            Node event = doc.createElement("eventtype");
            Node description = doc.createAttribute("description");
            Node typeid = doc.createAttribute("typeid");
            description.setNodeValue(et.getDescription());
            typeid.setNodeValue(et.getTypeId()+"");
            event.getAttributes().setNamedItem(description);
            event.getAttributes().setNamedItem(typeid);
            root.appendChild(event);
        }
        return getXML(doc);
    }

    private static Document getBaseDocument() throws ParserConfigurationException {
        Document doc = DocumentBuilderFactory.newInstance().newDocumentBuilder().newDocument();
        doc.appendChild(doc.createElement("crombz"));
        return doc;
    }

    private static String getXML(Document doc) throws TransformerConfigurationException, TransformerException {
        TransformerFactory tf = TransformerFactory.newInstance();
        Transformer transformer = tf.newTransformer();
        transformer.setOutputProperty(OutputKeys.OMIT_XML_DECLARATION, "yes");
        StringWriter writer = new StringWriter();
        transformer.transform(new DOMSource(doc), new StreamResult(writer));
        return writer.getBuffer().toString().replaceAll("\n|\r", "");
    }
}
